Re: Turning off transactions completely.

Hi,

> Using COPY for the init load and insert for subsequent handling. Can't use
> COPY there cause there are WHERE clauses in there I need.
>
> I start off with 3.3GB of data and things just grow from that with a fresh
> 3GB file every day! Plenty of dup data in it though. Lots of complex
> queries get run on this data, which generates even more info... At the
> end of the initial run, I get ~50GB of data in the pg data dir (course
> that's got  a lot of additional info than just a plain text file...) with
> ~10GB per subsequent run.

I would suggest to optimize your Application!

Try to:

  - cache previously inserted IDs and other things
    (eg. use Perl hashes)

  - create simpler Where clauses

  - look on your indexes, perhaps you can create an index on two columns?

And:
  use vacuum analyze after the Database is freshly build and filled
  with the first ~ 100 000 rows.
  Later vacuum analyze every 1 000 000 or 10 000 000 rows ...

> BTW, I've got a dual proc machine with a RAID-0 array and 1 GB of RAM, but
> pg only uses one CPU at a time. Would have been great if it had been
> multi-threaded or something.

if you use two inserting processes, Postgres also should use two. AFAIK! :)


Ciao
  Alvar


--
http://www.teletrust.info/
http://www.odem.org/ || http://www.odem.org/insert_coin/imkp2001.html


--
AGI :: Hohnerstrasse 23, 70469 Stuttgart
Fon +49 (0)711.490 320-0, Fax +49 (0)711.490 320-150
AGI auf Platz 3 im neuen Multimedia-Kreativranking
http://www.agi.de/tagebuch/